package dtd.PHS.VND_ExchangeRate.data;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import dtd.PHS.VND_ExchangeRate.UpdateRatesService;
import dtd.PHS.VND_ExchangeRate.utils.ApplicationContext;
import dtd.PHS.VND_ExchangeRate.utils.Helpers;
import dtd.PHS.VND_ExchangeRate.utils.Logger;
import dtd.PHS.VND_ExchangeRate.widget.WidgetItem;


public class VNExDatabase extends SQLiteOpenHelper {


	private static final String DEFAULT_DATE = "05/07/2011";
	private static final String DB_NAME = "VNExDatabase";
	private static final int DB_VERSION = 1;

	//======== Table: List rates ===============
	private static final String TABLE_LIST_RATES = "LIST_RATES_TABLE";	
	private static final String CURRENCY_NAME = "curr_name";
	private static final String SELLING = "selling";
	private static final String TRANSFER = "transfer";
	private static final String BUYING = "buying";
	private static final String[] LIST_RATES_FIELDS = {
		CURRENCY_NAME + " text",
		BUYING + " real",
		TRANSFER+ " real",
		SELLING + " real"	
	};

	private static final String TABLE_WIDGET = "WIDGET_TABLE";
	private static final String WIDGET_CURRENCY = "CURRENCY";
	private static final String APP_WIDGET_ID = "ID";
	private static final String[] WIDGET_FIELDS = {
		APP_WIDGET_ID + " INTEGER PRIMARY KEY",
		WIDGET_CURRENCY + " text"
	};


	//======== Table: List rates ===============

	private SQLiteDatabase database;

	public VNExDatabase() {
		super(ApplicationContext.getContext(), DB_NAME , null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		db.execSQL(createTable(TABLE_LIST_RATES, LIST_RATES_FIELDS));
		db.execSQL(createTable(TABLE_WIDGET, WIDGET_FIELDS));
		Logger.logInfo("Database created");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LIST_RATES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_WIDGET);
	}

	public static ListRates getListRates() {
		if ( !ListRatesCache.isCached() ) {
			updateCacheFromDB();
			if ( ListRatesCache.expired() ) {				
				startUpdateDataService();
			}
		}

		ListRates listRates = ListRatesCache.getListRates();
		listRates = ListRates.reorder(listRates);
		return listRates;
	}

	private static void startUpdateDataService() {
		Logger.logInfo("Starting the service ...");
		Intent i = new Intent(ApplicationContext.getContext(),UpdateRatesService.class);
		ApplicationContext.getContext().startService(i);
	}

	private static void updateCacheFromDB() {
		VNExDatabase db = new VNExDatabase();
		try {
			db.open();
			ListRates rates = db.getListRatesFromDB();		
			db.close();
			long createdTime = getCreatedTimeFromDB();
			ListRatesCache.setCreatedTime(createdTime);
			ListRatesCache.cacheListRates(rates);
			ListRatesCache.setDate(getDate());
		} catch (Exception e) {
			Logger.logError(e);
		}
	}

	private static long getCreatedTimeFromDB() {
		String strtime = Helpers.getPreference(Helpers.PREF_CREATED_TIME);
		Logger.logInfo("Time: "+strtime);

		return Long.parseLong(strtime);
	}

	private ListRates getListRatesFromDB() {
		Cursor cursor = null;
		ListRates list = new ListRates();
		try {
			cursor = database.query(TABLE_LIST_RATES, null, null, null, null, null, null);
			if ( cursor.moveToFirst() ) {
				while ( true ) {
					RateItem item = createItemFromCursor( cursor );
					list.add(item);					
					if ( ! cursor.moveToNext() ) break;
				}
			}
		} catch (Exception e) {
			Logger.logError(e);
		} finally {
			if ( cursor != null ) cursor.close();			
		}
		return list;



	}


	private RateItem createItemFromCursor(Cursor cursor) {
		String currencyName = cursor.getString(cursor.getColumnIndex(CURRENCY_NAME));
		double buying = cursor.getDouble(cursor.getColumnIndex(BUYING));
		double transfer = cursor.getDouble(cursor.getColumnIndex(TRANSFER));
		double selling = cursor.getDouble(cursor.getColumnIndex(SELLING));
		return new RateItem(currencyName,buying,transfer,selling);
	}

	public void open() {
		try {
			database = this.getWritableDatabase();
		} catch (SQLiteException e) {
			database = this.getReadableDatabase();
		}
	}	



	public static MyDate getDate() {
		if (ListRatesCache.getDate() == null) {
			String prefDate = Helpers.getPreference(Helpers.PREF_DATE);
			if ( prefDate == null ) 
				prefDate = DEFAULT_DATE;
			setDate(new MyDate(prefDate));
		};

		return ListRatesCache.getDate();
	}

	static final String[] static_rates = {
		"AUD/21875.68/22007.73/22349.28",
		"CAD/21122.27/21314.10/21644.89",
		"CHF/23954.23/24123.09/24497.48",
		"EUR/29669.65/29758.93/30160.40",
		"GBP/32636.84/32866.91/33310.31",
		"HKD/2602.05/2620.39/2671.73",
		"JPY/249.92/252.44/257.38",
		"SGD/16538.03/16654.61/16980.88",
		"THB/662.08/662.08/691.45",
		"USD/20550.00/20550.00/20620.00" 
	};
	static final MyDate createdDate = new MyDate(DEFAULT_DATE);
	static final String staticGoldPrice = "45900/46200";
	static final String createdGoldDate = "30/08/2011 21:00";


	public static void createDBFromStatic() {
		createStaticListRates();
		createStaticGoldPrice();
	}	

	private static void createStaticGoldPrice() {
		SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy hh:mm");
		Date createdTime = null;
		try {
			createdTime = df.parse(createdGoldDate);
		} catch (ParseException e) {
			Logger.logError(e);
		}
		String goldPriceStr = staticGoldPrice + "/"+createdTime.getTime()+"/"+createdTime.getTime();
		updateGoldPrice(new SJCItem(goldPriceStr));
	}

	private static void createStaticListRates() {
		ListRates list = new ListRates();
		for(String rateStr : static_rates) {
			RateItem item = new RateItem( rateStr );			
			list.add( item );
		}
		refreshDB(list,createdDate);
	}

	//... update cache
	public static void refreshDB(ListRates listRates, MyDate date) {
		VNExDatabase db = new VNExDatabase();
		try {
			db.open();
			db.refresh( listRates );
			db.close();
			setDate(date);
		} catch (Exception e) {
			Logger.logError(e);
		}
	}

	private void refresh(ListRates listRates) {
		this.database.delete(TABLE_LIST_RATES, null, null);
		for(int i  = 0 ; i < listRates.size() ; i++) {
			RateItem rate = listRates.get(i);
			insertRow(rate);
		}
		long createdTime = System.currentTimeMillis();
		setCreatedTimeDB(createdTime);

		//cache		
		ListRatesCache.cacheListRates(listRates);		
	}

	private void insertRow(RateItem rate) {
		ContentValues values = new ContentValues();		

		values.put(CURRENCY_NAME, rate.getCurrency().toString());
		values.put(SELLING, rate.getSelling());
		values.put(TRANSFER, rate.getTransfer());
		values.put(BUYING, rate.getBuying());

		database.insert(TABLE_LIST_RATES, null, values);	

	}

	private static void setDate(MyDate date) {
		Helpers.setPreference(Helpers.PREF_DATE, date.toString());
		ListRatesCache.setDate(date);
	}


	public static String createTable(
			String tableName,
			String[] fields) {
		StringBuilder builder = new StringBuilder("create table " + tableName + " (");
		for(int i = 0 ; i < fields.length ; i++) {
			builder.append( fields[i] );
			if ( i != fields.length - 1 ) {
				builder.append(" , ");
			}
		}
		builder.append(");");
		return builder.toString();
	}

	public static void setCreatedTimeDB(long createdTime) {
		Helpers.setPreference(Helpers.PREF_CREATED_TIME, String.valueOf(createdTime));
		ListRatesCache.setCreatedTime( createdTime );
	}

	/**
	 * Precondition: there is already a sjc gold price in the db (updated by first time running !)
	 * @return
	 */
	public static SJCItem getGoldRate() {
		if ( !GoldCache.isCached() ) {
			updateGoldCacheFromDB();
		}
		SJCItem price = GoldCache.getCachedPrice();
		if ( price.isExpired()  ) {
			startUpdateGoldPriceService();
		}
		return price;
	}

	private static void startUpdateGoldPriceService() {
		Intent i = new Intent(ApplicationContext.getContext(),UpdateGoldService.class);
		ApplicationContext.getContext().startService(i);

	}

	private static void updateGoldCacheFromDB() {
		SJCItem price = getGoldPriceFromDB();
		GoldCache.setCache(price);
	}

	/**
	 * Precondition: there is gold price in Pref, whenever the app started ! 
	 * @return
	 */
	static private SJCItem getGoldPriceFromDB() {
		String goldPrice = Helpers.getPreference(Helpers.PREF_GOLD_ITEM);
		return new SJCItem(goldPrice);
	}

	static void setGoldPriceInDB(SJCItem price) {
		Helpers.setPreference(Helpers.PREF_GOLD_ITEM, price.toString());
	}

	public static void updateGoldPrice(SJCItem price) {
		GoldCache.setCache(price);
		setGoldPriceInDB(price);
	}

	public static void insertWidgetCurrency(int appWidgetId, String currency) {
		VNExDatabase db = new VNExDatabase();
		try {
			db.open();
			db.insertWidgetCurrencyDB(appWidgetId,currency);
			db.close();
		} catch (Exception e) {
			Logger.logError(e);
		}
	}

	private void insertWidgetCurrencyDB(int appWidgetId, String currency) {
		ContentValues values = getCurrencyForWidget(appWidgetId);
		if ( values == null ) {
			values = new ContentValues();
			values.put(APP_WIDGET_ID, appWidgetId);
			values.put(WIDGET_CURRENCY, currency);
			database.insert(TABLE_WIDGET, null, values);
		} else {
			values.put(APP_WIDGET_ID, appWidgetId);
			values.put(WIDGET_CURRENCY,currency);
			database.update(TABLE_WIDGET, values, APP_WIDGET_ID + " = " + appWidgetId, null);
		}
	}

	private ContentValues getCurrencyForWidget(int appWidgetId) {
		ContentValues values = null;
		Cursor cursor = null;
		try {
			cursor = database.query(TABLE_WIDGET, null, APP_WIDGET_ID +" = " + appWidgetId, null, null, null, null);
			if ( cursor.moveToFirst() ) {
				values = new ContentValues();
				values.put(APP_WIDGET_ID, cursor.getInt(cursor.getColumnIndex(APP_WIDGET_ID)));
				values.put(WIDGET_CURRENCY, cursor.getInt(cursor.getColumnIndex(WIDGET_CURRENCY)));
				return values;
			} else return null;
		} catch (Exception e) {
			Logger.logError(e);
			return values;
		} finally {
			if ( cursor != null ) 
				cursor.close();
		}
	}

	public static ArrayList<WidgetItem> getAllWidgetInfo() {
		VNExDatabase db = new VNExDatabase();
		ArrayList<WidgetItem> items = new ArrayList<WidgetItem>();
		try {
			db.open();
			items = db.getAllWidget();
			db.close();
		} catch (Exception e) {
			Logger.logError(e);
		}
		return items;

	}

	private ArrayList<WidgetItem> getAllWidget() {
		ArrayList<WidgetItem> items = new ArrayList<WidgetItem>();
		Cursor cursor = null;
		try {
			cursor = database.query(TABLE_WIDGET, null, null, null, null, null, null);
			if ( cursor.moveToFirst() ) {
				while (true) {
					int id = cursor.getInt(cursor.getColumnIndex(APP_WIDGET_ID));
					String curr = cursor.getString(cursor.getColumnIndex(WIDGET_CURRENCY));
					WidgetItem widget = new WidgetItem(id,curr);
					items.add(widget);
					if ( ! cursor.moveToNext() ) break;
				}
			}
			return items;
		} catch (Exception e) {
			return items;
		} finally {
			if ( cursor != null ) 
				cursor.close();
		}
	}

	public static void deleteWidgets(int[] appWidgetIds) {
		VNExDatabase db = new VNExDatabase();
		try {
			db.open();
			db.deleteWidgetsInDB( appWidgetIds );
			db.close();
		} catch (Exception e) {
			Logger.logError(e);
		}
	}

	private void deleteWidgetsInDB(int[] appWidgetIds) {
		database.beginTransaction();
		for(int id : appWidgetIds) {
			try {
				database.delete(TABLE_WIDGET, APP_WIDGET_ID + " = " + id, null);
			} catch (Exception e) {
				Logger.logError(e);
			}
		}
		database.endTransaction();
	}


}
